<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
	<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
	<title>sql_accounts: common_schema documentation</title>
	<meta name="description" content="sql_accounts: common_schema" />
	<meta name="keywords" content="sql_accounts: common_schema" />
	<link rel="stylesheet" type="text/css" href="css/style.css" />
</head>

<body>
	<div id="main">
		<div id="header">
			<h1>common_schema</h1> <strong>2.2</strong> documentation
			<div class="subtitle">DBA's framework for MySQL</div>
		</div>
		<div id="contentwrapper">
			<div id="content">
				<h2><a href="sql_accounts.html">sql_accounts</a></h2>	
<h3>NAME</h3>
sql_accounts: Generate SQL statements to block/release accounts. Provide info on accounts.
<h3>TYPE</h3>
View

<h3>DESCRIPTION</h3>

<p>
	<i>sql_accounts</i> fills in a missing feature in MySQL: the ability to temporarily
 	block user accounts and release them, without tampering with their privileges.
 	It hacks its way by modifying and controlling accounts' passwords in a symmetric way.
</p>

<p>
	To block an account, this view generates a SQL query which changes the account's password 
	into a blocked-password value in such way that:
	<ul>
		<li>It is impossible to log in to the account with the original password</li>
		<li>It is impossible to log in to the account with any other password</li>
		<li>It is possible to detect that the password is a blocked-password</li>
		<li>It is possible to recover the original password</li>
	</ul>
	The SQL query to release a blocked account is likewise generated by this view.
</p>
<p>
	In fact, the view provides metadata for each account to explain accounts status: 
	is it blocked? Is it released? Is the password empty? Old?
</p>
<p>
	By generating the SQL statement to modify the account this view fall well onto
	use of <a href="eval.html">eval()</a> (see examples below).
</p>

<p>
	One should note that blocking accounts does not terminate existing account sessions.
	To kill existing sessions use the <a href="killall.html">killall()</a> routine.
</p>

<h3>STRUCTURE</h3>

<blockquote><pre>
mysql&gt; DESC common_schema.sql_accounts;
+---------------------+--------------+------+-----+---------+-------+
| Field               | Type         | Null | Key | Default | Extra |
+---------------------+--------------+------+-----+---------+-------+
| user                | char(16)     | NO   |     |         |       |
| host                | char(60)     | NO   |     |         |       |
| grantee             | varchar(100) | YES  |     | NULL    |       |
| password            | char(41)     | NO   |     |         |       |
| is_empty_password   | int(1)       | NO   |     | 0       |       |
| is_new_password     | int(1)       | NO   |     | 0       |       |
| is_old_password     | int(1)       | NO   |     | 0       |       |
| is_blocked          | int(1)       | NO   |     | 0       |       |
| sql_block_account   | longtext     | YES  |     | NULL    |       |
| sql_release_account | varchar(163) | YES  |     | NULL    |       |
+---------------------+--------------+------+-----+---------+-------+
</pre></blockquote>

<h3>SYNOPSIS</h3>

<p>Columns of this view:</p>
<ul>
	<li><strong>user</strong>: account user part</li>
	<li><strong>host</strong>: account host part</li>
	<li><strong>grantee</strong>: grantee name</li>
	<li><strong>password</strong>: current password for grantee (as in <strong>mysql.user</strong>)</li>
	<li><strong>is_empty_password</strong>: boolean, <strong>1</strong> if account's password is empty, <strong>0</strong> otherwise.</li>
	<li><strong>is_new_password</strong>: boolean, <strong>1</strong> if password is in new format, <strong>0</strong> otherwise.</li>
	<li><strong>is_old_password</strong>: boolean, <strong>1</strong> if password is in old format, <strong>0</strong> otherwise. 
		This is the opposite of <strong>is_new_password</strong>.
		<br/>The two columns are mostly informative. 
	</li>
	<li><strong>is_blocked</strong>: boolean, <strong>1</strong> if account is currently blocked (by logic of this very view), <strong>0</strong> otherwise.</li>
	<li><strong>sql_block_account</strong>: 
		A SQL (<strong>SET PASSWORD</strong>) query to block the account, making it inaccessible.
		<br/>The query has no effect on an account which is already blocked.	
		<br/>Use with <a href="eval.html">eval()</a> to apply query.	
	</li>
	<li><strong>sql_release_account</strong>: 
		A SQL (<strong>SET PASSWORD</strong>) query to release an account, re-enabling it.
		<br/>The query has no effect on an account which is not blocked.	
		<br/>Use with <a href="eval.html">eval()</a> to apply query.	
	</li>
</ul>

<h3>EXAMPLES</h3>

<p>Show info for <strong>'gromit'@'localhost'</strong> account:</p>
<blockquote><pre>mysql&gt; SELECT * FROM sql_accounts WHERE USER = 'gromit' \G

               user: gromit
               host: localhost
            grantee: 'gromit'@'localhost'
           password: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257
  is_empty_password: 0
    is_new_password: 1
    is_old_password: 0
         is_blocked: 0
  sql_block_account: SET PASSWORD FOR 'gromit'@'localhost' = '752AA50E562A6B40DE87DF0FA69FACADD908EA32*'
sql_release_account: SET PASSWORD FOR 'gromit'@'localhost' = '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257'
</pre></blockquote>

<p>Block all accounts with user <strong>'gromit'</strong>. Show info again:
<blockquote><pre>mysql&gt; CALL eval("SELECT sql_block_account FROM sql_accounts WHERE USER = 'gromit'");

mysql&gt; SELECT * FROM sql_accounts WHERE USER = 'gromit' \G

               user: gromit
               host: localhost
            grantee: 'gromit'@'localhost'
           password: 752AA50E562A6B40DE87DF0FA69FACADD908EA32*
  is_empty_password: 0
    is_new_password: 1
    is_old_password: 0
         is_blocked: 1
  sql_block_account: SET PASSWORD FOR 'gromit'@'localhost' = '752AA50E562A6B40DE87DF0FA69FACADD908EA32*'
sql_release_account: SET PASSWORD FOR 'gromit'@'localhost' = '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257'
</pre></blockquote>
Note that account's password is modified. It is modified to a value accepted by MySQL, but which
can never be generated by the 
<a href="http://dev.mysql.com/doc/refman/5.1/en/encryption-functions.html#function_password">PASSWORD()</a>
function. However, it is easily reversible.
</p>


<p>Release all blocked accounts. Check:
<blockquote><pre>mysql&gt; CALL eval("SELECT sql_release_account FROM sql_accounts");

mysql&gt; SELECT COUNT(*) AS count_blocked_accounts FROM sql_accounts WHERE is_blocked;
+------------------------+
| count_blocked_accounts |
+------------------------+
|                      0 |
+------------------------+
</pre></blockquote>
</p>


<h3>ENVIRONMENT</h3>
MySQL 5.1 or newer

<h3>SEE ALSO</h3>
<a href="eval.html">eval()</a>,
<a href="security_audit.html">security_audit()</a>,
<a href="sql_show_grants.html">sql_show_grants</a>
<h3>AUTHOR</h3>
Shlomi Noach
				<br/>
			</div>
			<div id="sidebarwrapper">
				<div id="search">
					Search online documentation
					<form id="search_form" name="search_form" method="GET" 
						action="http://www.google.com/search" 
						onsubmit="document.forms['search_form']['q'].value = 'site:http://common-schema.googlecode.com/svn/trunk/common_schema/doc/html/ '+document.forms['search_form']['search_term'].value;">
						<input type="text" name="search_term" value=""/>
						<input type="hidden" name="q" value=""/>
						<input type="submit" value="go"/>						
					</form>
				</div>
				<div id="menu">
					<ul>
						<li><a title="Introduction" href="introduction.html">Introduction</a></li>
						<li><a title="Documentation" href="documentation.html">Documentation</a></li>
						<li><a title="Download" href="download.html">Download</a></li>
						<li><a title="Install" href="install.html">Install</a></li>
						<li><a title="Risks" href="risks.html">Risks</a></li>
					</ul>						
					<h3>QUERY SCRIPT</h3>
					<ul>
						<li><a title="QueryScript" href="query_script.html">QueryScript</a></li>
						<li><a title="Execution" href="query_script_execution.html">Execution</a></li>
						<li><a title="Flow control" href="query_script_flow_control.html">Flow control</a></li>
						<li><a title="Statements" href="query_script_statements.html">Statements</a></li>
						<li><a title="Expressions" href="query_script_expressions.html">Expressions</a></li>
						<li><a title="Variables" href="query_script_variables.html">Variables</a></li>
					</ul>						
					<h3>DEBUG</h3>
					<ul>
						<li><a title="rdebug" href="rdebug.html">rdebug</a></li>
						<li><a title="rdebug API" href="rdebug_api.html">rdebug API</a></li>
						<li><a title="rdebug workflow" href="rdebug_workflow.html">Workflow</a></li>
					</ul>						
					<h3>ROUTINES</h3>
					<ul>
						<li><a title="Execution &amp; flow control" href="execution_routines.html">Execution & flow control</a></li>
						<li><a title="General" href="general_routines.html">General</a></li>
						<li><a title="Process" href="process_routines.html">Process</a></li>
						<li><a title="Query analysis" href="query_analysis_routines.html">Query analysis</a></li>
						<li><a title="Schema analysis" href="schema_analysis_routines.html">Schema analysis</a></li>
						<li><a title="Security" href="security_routines.html">Security</a></li>
						<li><a title="Text" href="text_routines.html">Text</a></li>
						<li><a title="Time &amp; date" href="temporal_routines.html">Time & date</a></li>
						<li><a title="Charting" href="charting_routines.html">Charting</a></li>
					</ul>
					<h3>VIEWS</h3>
					<ul>
						<li><a title="Schema analysis" href="schema_analysis_views.html">Schema analysis</a></li>
						<li><a title="Data dimension" href="data_dimension_views.html">Data dimension</a></li>
						<li><a title="Process" href="process_views.html">Process</a></li>
						<li><a title="Security" href="security_views.html">Security</a></li>
						<li><a title="Monitoring" href="monitoring_views.html">Monitoring</a></li>
						<li><a title="InnoDB Plugin" href="innodb_plugin_views.html">InnoDB Plugin</a></li>
						<li><a title="Percona server" href="percona_server_views.html">Percona Server</a></li>
						<li><a title="TokuDB" href="tokudb_views.html">TokuDB</a></li>
					</ul>						
					<h3>DATA</h3>
					<ul>
						<li><a title="tables" href="tables.html">Tables</a></li>
						<li><a title="variables" href="variables.html">Variables</a></li>
					</ul>						
					<h3>META</h3>
					<ul>
						<li><a title="Help" href="help.html">help</a></li>
						<li><a title="Metadata" href="metadata.html">metadata</a></li>
						<li><a title="status" href="status.html">status</a></li>
					</ul>						
				</div>
			</div>	
			<div class="clear">&nbsp;</div>
			
			<div id="footnote" align="center">
				<a href="">common_schema</a> documentation
			</div>
		</div>
	</div>
</body>
</html>
